﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class MeetingDao
    {
        public bool Delete(SqlConnection conn, int meetingId, string userId, bool isAdministrator)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF @IsAdministrator =1 ");
            builder.Append("\tDELETE FROM [dbo].[Meeting] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[MeetingId] = @MeetingId ");
            builder.Append("ELSE\t ");
            builder.Append("\tDELETE FROM [dbo].[Meeting] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[MeetingId] = @MeetingId ");
            builder.Append("\t\tAND [Proposer] = @UserId ");
            builder.Append("\t\tAND [Status] IN(0,2) ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@MeetingId", SqlDbType.Int);
            commandParameters[0].Value = meetingId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            commandParameters[2] = new SqlParameter("@IsAdministrator", SqlDbType.Bit);
            commandParameters[2].Value = isAdministrator;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public string GetOperator(SqlConnection conn)
        {
            string commandText = "SELECT ISNULL([ParaValue],'') FROM [dbo].[Sys_Para] WHERE [ParaName]='MEETING_OPERATOR'";
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText);
            if (obj2 != null)
            {
                return Convert.ToString(obj2);
            }
            return string.Empty;
        }

        public int Insert(SqlConnection conn, MeetingData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Meeting] ");
            builder.Append("( ");
            builder.Append("\t[RoomId], ");
            builder.Append("\t[Name], ");
            builder.Append("\t[Topic], ");
            builder.Append("\t[Description], ");
            builder.Append("\t[Proposer], ");
            builder.Append("\t[RequestTime], ");
            builder.Append("\t[Attendee], ");
            builder.Append("\t[Guest], ");
            builder.Append("\t[StartTime], ");
            builder.Append("\t[EndTime], ");
            builder.Append("\t[SmsRemind], ");
            builder.Append("\t[MobileRemind], ");
            builder.Append("\t[Status] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@RoomId, ");
            builder.Append("\t@Name, ");
            builder.Append("\t@Topic, ");
            builder.Append("\t@Description, ");
            builder.Append("\t@Proposer, ");
            builder.Append("\t@RequestTime, ");
            builder.Append("\t@Attendee, ");
            builder.Append("\t@Guest, ");
            builder.Append("\t@StartTime, ");
            builder.Append("\t@EndTime, ");
            builder.Append("\t@SmsRemind, ");
            builder.Append("\t@MobileRemind, ");
            builder.Append("\t@Status ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[13];
            commandParameters[0] = new SqlParameter("@RoomId", SqlDbType.Int);
            commandParameters[0].Value = data.RoomId;
            commandParameters[1] = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
            commandParameters[1].Value = data.Name;
            commandParameters[2] = new SqlParameter("@Topic", SqlDbType.NVarChar, 200);
            if (data.TopicIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.Topic;
            }
            commandParameters[3] = new SqlParameter("@Description", SqlDbType.NText);
            if (data.DescriptionIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.Description;
            }
            commandParameters[4] = new SqlParameter("@Proposer", SqlDbType.NVarChar, 20);
            commandParameters[4].Value = data.Proposer;
            commandParameters[5] = new SqlParameter("@RequestTime", SqlDbType.DateTime);
            commandParameters[5].Value = data.RequestTime;
            commandParameters[6] = new SqlParameter("@Attendee", SqlDbType.NText);
            if (data.AttendeeIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.Attendee;
            }
            commandParameters[7] = new SqlParameter("@Guest", SqlDbType.NText);
            if (data.GuestIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Guest;
            }
            commandParameters[8] = new SqlParameter("@StartTime", SqlDbType.DateTime);
            commandParameters[8].Value = data.StartTime;
            commandParameters[9] = new SqlParameter("@EndTime", SqlDbType.DateTime);
            commandParameters[9].Value = data.EndTime;
            commandParameters[10] = new SqlParameter("@SmsRemind", SqlDbType.Bit);
            commandParameters[10].Value = data.SmsRemind;
            commandParameters[11] = new SqlParameter("@MobileRemind", SqlDbType.Bit);
            commandParameters[11].Value = data.MobileRemind;
            commandParameters[12] = new SqlParameter("@Status", SqlDbType.Int);
            commandParameters[12].Value = data.Status;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public bool Manage(SqlConnection conn, int meetingId, int operation)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Meeting] ");
            builder.Append("SET ");
            builder.Append("\t[Status] = @Operation ");
            builder.Append("WHERE ");
            builder.Append("\t[MeetingId] = @MeetingId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@MeetingId", SqlDbType.Int);
            commandParameters[0].Value = meetingId;
            commandParameters[1] = new SqlParameter("@Operation", SqlDbType.Int);
            commandParameters[1].Value = operation;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public DataTable SelectApplyAll(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[Meeting].[MeetingId],\t ");
            builder.Append("\t[Meeting].[Name], ");
            builder.Append("\t[Meeting].[Topic],\t ");
            builder.Append("\t[Meeting].[StartTime], ");
            builder.Append("\t[Meeting].[EndTime], ");
            builder.Append("\t[Meeting_Room].[Name] AS RoomName, ");
            builder.Append("\t[Meeting].[Status] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Meeting], ");
            builder.Append("\t[dbo].[Meeting_Room] ");
            builder.Append("WHERE ");
            builder.Append("\t[Meeting].[RoomId] = [Meeting_Room].[RoomId] ");
            builder.Append("\tAND [Meeting].[Proposer] = @UserId ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Meeting].[MeetingId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Meeting";
            return set.Tables[0];
        }

        public DataTable SelectApplyPaged(SqlConnection conn, int pagesize, int pageindex, bool docount, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT COUNT([MeetingId]) FROM [dbo].[Meeting] WHERE [Proposer] = @UserId AND [Status] = 1 ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [MeetingId] FROM [dbo].[Meeting] WHERE [Proposer] = @UserId AND [Status] = 1 ORDER BY [MeetingId] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[Meeting].[MeetingId],\t\t\t ");
            builder.Append("\t\t\t[Meeting].[Name], ");
            builder.Append("\t\t\t[Meeting].[Topic],\t\t\t ");
            builder.Append("\t\t\t[Users].[UserName] AS ProposerName,\t\t\t ");
            builder.Append("\t\t\t[Meeting].[StartTime], ");
            builder.Append("\t\t\t[Meeting_Room].[Name] AS RoomName,\t ");
            builder.Append("\t\t\t[Meeting].[Status], ");
            builder.Append("\t\t\t@pageindex AS pageindex ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Meeting], [dbo].[Meeting_Room], [dbo].[Users],@IndexTable t\t\t\t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[Meeting].[Proposer] = @UserId ");
            builder.Append("\t\t\tAND [Meeting].[RoomId] = [Meeting_Room].[RoomId] ");
            builder.Append("\t\t\tAND [Meeting].[Proposer] = [Users].[UserId] ");
            builder.Append("\t\t\tAND [Meeting].[Status] = 1 ");
            builder.Append("\t\t\tAND [Meeting].[MeetingId] = t.PK\t\t\t ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound  ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound  ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[Meeting].[MeetingId] DESC\t\t\t ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            commandParameters[3] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[3].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Meeting";
            return set.Tables[0];
        }

        public DataTable SelectByMeetingId(SqlConnection conn, int meetingId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[Meeting].[MeetingId], ");
            builder.Append("\t[Meeting].[RoomId], ");
            builder.Append("\t[Meeting_Room].[Name] AS RoomName, ");
            builder.Append("\t[Meeting].[Name], ");
            builder.Append("\t[Meeting].[Topic], ");
            builder.Append("\t[Meeting].[Description], ");
            builder.Append("\t[Meeting].[Proposer], ");
            builder.Append("\t[Users].[UserName] AS ProposerName, ");
            builder.Append("\t[Meeting].[RequestTime], ");
            builder.Append("\t[Meeting].[Attendee], ");
            builder.Append("\tdbo.GetUserNames(CAST([Meeting].[Attendee] AS nvarchar(4000))) AttendeeName, ");
            builder.Append("\t[Meeting].[Guest], ");
            builder.Append("\t[Meeting].[StartTime], ");
            builder.Append("\t[Meeting].[EndTime], ");
            builder.Append("\t[Meeting].[SmsRemind], ");
            builder.Append("\t[Meeting].[MobileRemind], ");
            builder.Append("\t[Meeting].[Status] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Meeting],  ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Meeting_Room] ");
            builder.Append("WHERE ");
            builder.Append("\t[Meeting].[MeetingId] = @MeetingId ");
            builder.Append("\tAND [Meeting].[RoomId] = [Meeting_Room].[RoomId] ");
            builder.Append("\tAND [Meeting].[Proposer] = [Users].[UserId] ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@MeetingId", SqlDbType.Int) };
            commandParameters[0].Value = meetingId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Meeting";
            return set.Tables[0];
        }

        public DataTable SelectNoAll(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[Meeting].[MeetingId],\t ");
            builder.Append("\t[Meeting].[Name], ");
            builder.Append("\t[Meeting].[Topic], ");
            builder.Append("\t[Users].[UserName] AS ProposerName, ");
            builder.Append("\t[Department].[DeptName], ");
            builder.Append("\t[Meeting].[StartTime], ");
            builder.Append("\t[Meeting].[EndTime], ");
            builder.Append("\t[Meeting_Room].[Name] AS RoomName,\t ");
            builder.Append("\t[Meeting].[Status] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Meeting],[dbo].[Meeting_Room], [dbo].[Users], [dbo].[Department] ");
            builder.Append("WHERE ");
            builder.Append("\t[Meeting].[RoomId] = [Meeting_Room].[RoomId] ");
            builder.Append("\tAND [Meeting].[Proposer] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("\tAND [Meeting].[Status] = 2 ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Meeting].[MeetingId] DESC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Meeting";
            return set.Tables[0];
        }

        public DataTable SelectNowAll(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[Meeting].[MeetingId],\t ");
            builder.Append("\t[Meeting].[Name], ");
            builder.Append("\t[Meeting].[Topic], ");
            builder.Append("\t[Users].[UserName] AS ProposerName, ");
            builder.Append("\t[Department].[DeptName], ");
            builder.Append("\t[Meeting].[StartTime], ");
            builder.Append("\t[Meeting].[EndTime], ");
            builder.Append("\t[Meeting_Room].[Name] AS RoomName,\t ");
            builder.Append("\t[Meeting].[Status] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Meeting],[dbo].[Meeting_Room], [dbo].[Users], [dbo].[Department] ");
            builder.Append("WHERE ");
            builder.Append("\t[Meeting].[RoomId] = [Meeting_Room].[RoomId] ");
            builder.Append("\tAND [Meeting].[Proposer] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("\tAND [Meeting].[Status] = 1 ");
            builder.Append("\tAND [Meeting].[StartTime] < GETDATE() ");
            builder.Append("\tAND [Meeting].[EndTime] > GETDATE() ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Meeting].[MeetingId] DESC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Meeting";
            return set.Tables[0];
        }

        public DataTable SelectOperator(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[Users].[UserId], ");
            builder.Append("\t[Users].[UserName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Sys_Para] ");
            builder.Append("WHERE ");
            builder.Append("\t[Sys_Para].[ParaName] = 'MEETING_OPERATOR' ");
            builder.Append("\tAND CHARINDEX(',' + [Users].[UserId] + ',',','+ ISNULL(CAST([Sys_Para].[ParaValue] AS nvarchar(2000)),'') + ',') > 0\t ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Users].[UserNo] ASC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Users";
            return set.Tables[0];
        }

        public DataTable SelectPaged(SqlConnection conn, int pagesize, int pageindex, bool docount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT COUNT([MeetingId]) FROM [dbo].[Meeting] WHERE [Status]=1 AND [StartTime] < GETDATE()");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [MeetingId] FROM [dbo].[Meeting] WHERE [Status]=1 AND [StartTime] < GETDATE() ORDER BY [MeetingId] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[Meeting].[MeetingId],\t\t\t ");
            builder.Append("\t\t\t[Meeting].[Name], ");
            builder.Append("\t\t\t[Meeting].[Topic],\t\t\t ");
            builder.Append("\t\t\t[Users].[UserName] AS ProposerName,\t\t\t ");
            builder.Append("\t\t\t[Department].[DeptName], ");
            builder.Append("\t\t\t[Meeting].[StartTime], ");
            builder.Append("\t\t\t[Meeting_Room].[Name] AS RoomName,\t ");
            builder.Append("\t\t\t[Meeting].[Status], ");
            builder.Append("\t\t\t@pageindex AS pageindex ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Meeting], [dbo].[Meeting_Room], [dbo].[Users], [dbo].[Department], @IndexTable t\t\t\t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[Meeting].[RoomId] = [Meeting_Room].[RoomId] ");
            builder.Append("\t\t\tAND [Meeting].[Proposer] = [Users].[UserId] ");
            builder.Append("\t\t\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("\t\t\tAND [Meeting].[Status] = 1 ");
            builder.Append("\t\t\tAND [Meeting].[MeetingId] = t.PK\t\t\t ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound  ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound  ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[Meeting].[MeetingId] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Meeting";
            return set.Tables[0];
        }

        public DataTable SelectWaitAll(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT ");
            builder.Append("\t[Meeting].[MeetingId],\t ");
            builder.Append("\t[Meeting].[Name], ");
            builder.Append("\t[Meeting].[Topic], ");
            builder.Append("\t[Users].[UserName] AS ProposerName, ");
            builder.Append("\t[Department].[DeptName], ");
            builder.Append("\t[Meeting].[StartTime], ");
            builder.Append("\t[Meeting].[EndTime], ");
            builder.Append("\t[Meeting_Room].[Name] AS RoomName,\t ");
            builder.Append("\t[Meeting].[Status] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Meeting],[dbo].[Meeting_Room], [dbo].[Users], [dbo].[Department] ");
            builder.Append("WHERE ");
            builder.Append("\t[Meeting].[RoomId] = [Meeting_Room].[RoomId] ");
            builder.Append("\tAND [Meeting].[Proposer] = [Users].[UserId] ");
            builder.Append("\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("\tAND [Meeting].[Status] = 0\t ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Meeting].[MeetingId] DESC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Meeting";
            return set.Tables[0];
        }

        public DataTable Today(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("\t ");
            builder.Append("SELECT ");
            builder.Append("\t[Meeting].[MeetingId],\t\t\t ");
            builder.Append("\t[Meeting].[Name], ");
            builder.Append("\t[Meeting].[Topic],\t\t\t ");
            builder.Append("\t[Users].[UserName] AS ProposerName,\t\t\t ");
            builder.Append("\t[Meeting].[StartTime], ");
            builder.Append("\t[Meeting].[EndTime], ");
            builder.Append("\t[Meeting_Room].[Name] AS RoomName,\t ");
            builder.Append("\t[Meeting].[Status]\t\t\t ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Meeting], ");
            builder.Append("\t[dbo].[Meeting_Room], ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE ");
            builder.Append("\t[Meeting].[RoomId] = [Meeting_Room].[RoomId] ");
            builder.Append("\tAND [Meeting].[Proposer] = [Users].[UserId] ");
            builder.Append("\tAND [Meeting].[Status] = 1 ");
            builder.Append("\tAND Datediff(day, [StartTime], GETDATE())=0 \t ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Meeting].[MeetingId] DESC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Meeting";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, MeetingData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Meeting] ");
            builder.Append("SET ");
            builder.Append("\t[RoomId] = @RoomId, ");
            builder.Append("\t[Name] = @Name, ");
            builder.Append("\t[Topic] = @Topic, ");
            builder.Append("\t[Description] = @Description, ");
            builder.Append("\t[Proposer] = @Proposer, ");
            builder.Append("\t[RequestTime] = @RequestTime, ");
            builder.Append("\t[Attendee] = @Attendee, ");
            builder.Append("\t[Guest] = @Guest, ");
            builder.Append("\t[StartTime] = @StartTime, ");
            builder.Append("\t[EndTime] = @EndTime, ");
            builder.Append("\t[SmsRemind] = @SmsRemind, ");
            builder.Append("\t[MobileRemind] = @MobileRemind, ");
            builder.Append("\t[Status] = @Status ");
            builder.Append("WHERE ");
            builder.Append("\t[MeetingId] = @MeetingId ");
            SqlParameter[] commandParameters = new SqlParameter[14];
            commandParameters[0] = new SqlParameter("@MeetingId", SqlDbType.Int);
            commandParameters[0].Value = data.MeetingId;
            commandParameters[1] = new SqlParameter("@RoomId", SqlDbType.Int);
            commandParameters[1].Value = data.RoomId;
            commandParameters[2] = new SqlParameter("@Name", SqlDbType.NVarChar, 50);
            commandParameters[2].Value = data.Name;
            commandParameters[3] = new SqlParameter("@Topic", SqlDbType.NVarChar, 200);
            if (data.TopicIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.Topic;
            }
            commandParameters[4] = new SqlParameter("@Description", SqlDbType.NText);
            if (data.DescriptionIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Description;
            }
            commandParameters[5] = new SqlParameter("@Proposer", SqlDbType.NVarChar, 20);
            commandParameters[5].Value = data.Proposer;
            commandParameters[6] = new SqlParameter("@RequestTime", SqlDbType.DateTime);
            commandParameters[6].Value = data.RequestTime;
            commandParameters[7] = new SqlParameter("@Attendee", SqlDbType.NText);
            if (data.AttendeeIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Attendee;
            }
            commandParameters[8] = new SqlParameter("@Guest", SqlDbType.NText);
            if (data.GuestIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.Guest;
            }
            commandParameters[9] = new SqlParameter("@StartTime", SqlDbType.DateTime);
            commandParameters[9].Value = data.StartTime;
            commandParameters[10] = new SqlParameter("@EndTime", SqlDbType.DateTime);
            commandParameters[10].Value = data.EndTime;
            commandParameters[11] = new SqlParameter("@SmsRemind", SqlDbType.Bit);
            commandParameters[11].Value = data.SmsRemind;
            commandParameters[12] = new SqlParameter("@MobileRemind", SqlDbType.Bit);
            commandParameters[12].Value = data.MobileRemind;
            commandParameters[13] = new SqlParameter("@Status", SqlDbType.Int);
            commandParameters[13].Value = data.Status;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public void UpdateOperator(SqlConnection conn, string strOperator)
        {
            string commandText = "UPDATE [dbo].[Sys_Para] SET [ParaValue] = '" + strOperator + "'  WHERE [ParaName]='MEETING_OPERATOR'";
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText);
        }
    }
}

